<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@page import="java.sql.CallableStatement"%>
<%@ page import="java.util.Vector" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Trial Balance</title>

<script type="text/javascript" src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script type="text/javascript" src="http://code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
<link href="globalCSS.css" rel="stylesheet" type="text/css"/>
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.css">


<style>
	.hidden 
	{
		display : none
	}
</style>
</head>

<body>
<h2 align="center">Trial Balance Report</h2>
<form name="fr2" method="get" action="TrialBalanceServlet3">

<div>
<table align="center" border=5 > 
<%  DBConnection dbc=new DBConnection();
			Connection con=dbc.getNewConnection();
			Statement st = null;
			ResultSet rs = null;
			
			int count=1;
			double debtors=0, creditors=0, cashbank=0, fixedassets=0, other=0, capital=0, otherexpenses=0, sellingandmarketing=0, salaryandwages=0, administrativecharges=0, provision=0, unsecuredloans=0, securedloans=0, investments=0, closingstock=0, openingstock=0, loansandadvances=0, indirectincome=0, bankchargesandinterest=0, depreciation=0; //trial balance
			
			double voucher=0.0,v3=0.0;
			
			String v5="",b5="",d="",d1="",d2="",d3="",min="",max="";
			%>
			<%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MIN(DATE) FROM ( (SELECT date as DATE FROM salesvoucher2) UNION ALL (SELECT  date as DATE FROM CreditNote) UNION ALL (SELECT date as DATE FROM purchasevoucher2) UNION ALL (SELECT date as DATE FROM DebitNote) UNION ALL (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) UNION ALL (SELECT date as DATE FROM journalvoucher2) ) p");
			
			while(rs.next()){
			
						 			
						        min=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}//code	        
						     %>
						     
						     
						     <%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MAX(DATE) FROM ( (SELECT date as DATE FROM salesvoucher2) UNION ALL (SELECT  date as DATE FROM CreditNote) UNION ALL (SELECT date as DATE FROM purchasevoucher2) UNION ALL (SELECT date as DATE FROM DebitNote) UNION ALL (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) UNION ALL (SELECT date as DATE FROM journalvoucher2) ) p");
			
			while(rs.next()){
			
						 			
						        max=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}	        
						     %>
						     
<tr>
    <td colspan=5> &nbsp &nbsp From:<%=min %> <br>  
			       &nbsp &nbsp To:<%=max %>
</tr>
<tbody>
<tr>
		<th> Sr. Number </th>
		<th> Account Name </th>
		<th> Group </th>
 		<th> ClosingBalance </th>
 		<th> Opening Balance </th>
</tr>


		
			<%
			try
			{
				double openingbalance=0.0;

				   CallableStatement cs = null;
				   cs=con.prepareCall("{call TB}");
				   rs=cs.executeQuery();
				   
				 	while(rs.next())
				 	{
					    String v1=rs.getString(1); // code
					    String v2=rs.getString(2); // name
					    v3=rs.getDouble(3); // amount
					    String v4=rs.getString(4); // group
					    
					    if(v1.equals("Sales Net Amount"))
					   	{
						   
					   	}
					    else if(v4.equals("Sundry Debtors"))
					   	{
						   
					   	}
			%>
<tr>
					<td> <%=count%> </td> <%count++; %>
					<td> <a href="http://localhost:8081/BOA_Web/TrialBalanceServlet2?code=<%=v1%>&name=<%=v2%>&group=<%=v4%>"> <%=v2%> </a></td>	
					<td> <%=v4%> </td>
					<td align="right"> <%=v3%> </td>	
					<td align="right""> <%=openingbalance %> </td>	
																	
<%			  	
					voucher=voucher+v3;
            	 } 
       		  	
       		  } 
            catch(Exception e) {} %>            
</tr> 

<tr>
	<td> <%=count %> <% count++; %>
	<td> Total </td>
	<td>  </td>
	<td align="right">  </td>

</tr>

</tbody>    
</table>
</div>

<script>
$('tr').each(function () {
	  a = $(this).children('td:eq(3)').text();
	  if (a == "0.0") {
	      $(this).addClass('hidden');
	  }
	})
</script>

<br>
<br>
From <input type="text" name="fromdate"/> To <input type="text" name="todate" /><input type="submit" value="Generate"/>

</form>

</body>
</html>